<?php
include("../header.inc");
require_once("../DataBase.inc");
?>
<form method="POST" enctype="multipart/form-data">
    Import Exam Results (CSV file from AMC):<br/>
    <table>
        <tr><td>Exam Center</td><td>
                <select name='exam_center_id' onchange='this.form.submit()'>
                    <option value=''></option>
                    <?php
                    $exam_centers = DataBase::query("SELECT exam_center.*,province.province_name FROM exam_center LEFT JOIN province ON province.province_id=exam_center.province_id WHERE exam_center.selection_campaign_id=" . $_SESSION["campaign_id"] . " ORDER BY province.province_name ASC");
                    foreach ($exam_centers as $center)
                        echo "<option value='" . $center["exam_center_id"] . "' " . (isset($_POST["exam_center_id"]) && $_POST["exam_center_id"] == $center["exam_center_id"] ? "selected='selected'" : "") . ">" . $center["province_name"] . " : " . $center["exam_center_name"] . "</option>";
                    ?>
                </select>
            </td></tr>
        <tr><td>Room</td><td>
                <?php
                    if (isset($_POST["exam_center_id"]) && $_POST["exam_center_id"] <> "") {
                        echo "<select name='room'>";
                        $rooms = DataBase::query("SELECT * FROM exam_center_rooms WHERE exam_center_id=" . $_POST["exam_center_id"]);
                        foreach ($rooms as $room)
                            echo "<option value=\"" . $room["room"] . "\" " . (isset($_POST["room"]) && $_POST["room"] == $room["room"] ? "selected='selected'" : "") . ">" . $room["room"] . "</option>";
                        echo "</select>";
                    }
                ?>
                </td></tr>
            <tr><td>Exam Version</td><td>
                <?php if (isset($_POST["exam_center_id"]) && $_POST["exam_center_id"] <> "") {
                ?>
                        <select name='exam_version'>
                            <option value=''></option>
                            <option value='A'<?php if (isset($_POST["exam_version"]) && $_POST["exam_version"] == "A")
                            echo " selected='selected'" ?>>A</option>
                    <option value='B'<?php if (isset($_POST["exam_version"]) && $_POST["exam_version"] == "B")
                                echo " selected='selected'" ?>>B</option>
                    </select>
                <?php } ?>
                        </td></tr>
                    <tr><td>Subject</td><td>
                <?php
                            if (isset($_POST["exam_center_id"]) && $_POST["exam_center_id"] <> "") {
                                echo "<select name='exam_copie_id'>";
                                $copies = DataBase::query("SELECT * FROM exam_copie WHERE selection_campaign_id=" . $_SESSION["campaign_id"]);
                                foreach ($copies as $copie)
                                    echo "<option value=\"" . $copie["exam_copie_id"] . "\" " . (isset($_POST["exam_copie_id"]) && $_POST["exam_copie_id"] == $copie["exam_copie_id"] ? "selected='selected'" : "") . ">" . $copie["exam_copie_name"] . "</option>";
                                echo "</select>";
                            }
                ?>
                        </td></tr>
                    <tr><td>CSV File</td><td>
                <?php if (isset($_POST["exam_center_id"]) && $_POST["exam_center_id"] <> "") {
                ?>
                                <input type='file' name='file'/>
                <?php } ?>
                        </td></tr>
                    <tr><td colspan=2 align='center'><button type="submit">Import</button></td></tr>
                </table>
            </form>
<?php
                            if (isset($_FILES['file'])) {
                                $error = $_FILES['file']['error'];
                                if ($error <> 0) {
                                    echo("An error occured while uploading the file: ");
                                    switch ($error) {
                                        case 1: die("File is too big (more than " . ini_get("upload_max_filesize") . "). Please check with administrator to increase this limit if necessary.");
                                        case 3: die("File has been only partially uploaded. Please retry.");
                                        case 4: die("No file was uploaded.");
                                        default: die("Error #" . $error . ". Please check with administrator to resolve this issue.");
                                    }
                                }
                                $filename = $_FILES['file']['tmp_name'];

                                $now = time();
                                DataBase::insert("concurrency", array("operation" => "import_amc", "timestamp" => $now, "username" => $_SESSION["user"]));
                                $res = DataBase::query("SELECT * FROM concurrency WHERE operation='import_amc' AND username<>'" . $_SESSION["user"] . "'");
                                if ($res <> false && count($res) > 0) {
                                    DataBase::delete("concurrency", array("operation" => "import_amc", "username" => $_SESSION["user"]));
                                    echo "User " . $res[0]["username"] . " is currently importing exam results at the same as you!<br/>";
                                    echo "Only one import can be done at the same time to avoid duplicates and errors while updating the database.<br/>";
                                    echo "Please wait a few seconds an try again or synchronize with this user.<br/>";
                                    die();
                                }

                                if (!isset($_POST["exam_center_id"]))
                                    die("No exam center specified");
                                $exam_center_id = $_POST["exam_center_id"];
                                if (!isset($_POST["room"]))
                                    die("No room specified");
                                $room = $_POST["room"];
                                if (!isset($_POST["exam_version"]))
                                    die("No exam version specified");
                                $exam_version = $_POST["exam_version"];
                                if (!isset($_POST["exam_copie_id"]))
                                    die("No subject specified");
                                $exam_copie_id = $_POST["exam_copie_id"];

                                $content = file_get_contents($filename);
                                $lines = explode("\n", $content);

                                function split_csv($line) {
                                    $len = strlen($line);
                                    $values = array();
                                    $value = "";
                                    $quote = false;
                                    for ($pos = 0; $pos < $len; $pos++) {
                                        if ($quote) {
                                            if ($line[$pos] == "\"") {
                                                $quote = false;
                                                continue;
                                            }
                                            if ($line[$pos] == "\\" && $pos < $len - 1)
                                                $pos++;
                                            $value .= $line[$pos];
                                            continue;
                                        }
                                        if ($line[$pos] == "\"") {
                                            $quote = true;
                                            continue;
                                        }
                                        if ($line[$pos] == ",") {
                                            array_push($values, $value);
                                            $value = "";
                                            continue;
                                        }
                                        $value .= $line[$pos];
                                    }
                                    array_push($values, $value);
                                    return $values;
                                }

                                $parts = DataBase::query("SELECT * FROM exam_copie_content LEFT JOIN subject_part ON subject_part.subject_part_id=exam_copie_content.exam_copie_subject_part_id WHERE exam_copie_content.exam_copie_id=" . $exam_copie_id . " ORDER BY exam_copie_content.exam_copie_part_number");
                                if ($parts == false || count($parts) == 0)
                                    die("Invalid subject: no parts.");
                                $nb_questions = 0;
                                foreach ($parts as $part)
                                    $nb_questions += $part["subject_part_nb_questions"];

                                echo count($lines) . " lines found<br/>";
                                if (substr($lines[0], 0, 7) == "\"nom,\"\"") {
                                    echo "File is invalid: try to automatically fix the problem<br/>";
                                    for ($i = 0; $i < count($lines); $i++) {
                                        $lines[$i] = substr($lines[$i], 1);
                                        $lines[$i] = str_replace("\"\"", "\"", $lines[$i]);
                                        if (substr($lines[$i], strlen($lines[$i]) - 2) == ",\"")
                                            $lines[$i] = substr($lines[$i], 0, strlen($lines[$i]) - 2);
                                    }
                                }
                                $titles = split_csv($lines[0]);
                                if (count($titles) <> $nb_questions + 5)
                                    die("File contains " . (count($titles) - 5) . " questions, but the subject contains " . $nb_questions . "!");
                                $max_marks = split_csv($lines[1]);
                                $errors = false;
                                $students = array();
                                for ($i = 3; $i < count($lines); ++$i) {
                                    if (trim($lines[$i]) == "")
                                        continue;
                                    $fields = split_csv($lines[$i]);
                                    if (count($fields) <> count($titles)) {
                                        echo "Error: line " . ($i + 1) . " contains " . count($fields) . " values, while the title row contains " . count($titles) . " columns!<br/>";
                                        $errors = true;
                                        continue;
                                    }
                                    $student_id = $fields[count($fields) - 1];
                                    $student_id = str_pad(trim($student_id), 4, "0", STR_PAD_LEFT);
                                    if (isset($students[$student_id])) {
                                        echo "Error: line " . ($i + 1) . ": student " . $student_id . " has a duplicate entry!<br/>";
                                        $errors = true;
                                        continue;
                                    }
                                    $r = DataBase::query("SELECT * FROM applicant LEFT JOIN applicant_exam_room_beta ON applicant.applicant_id=applicant_exam_room_beta.applicant_id WHERE applicant.applicant_selection_id='" . $student_id . "' AND selection_campaign_id=" . $_SESSION["campaign_id"]);
                                    if ($r == false || count($r) == 0) {
                                        echo "Error: line " . ($i + 1) . ": student " . $student_id . " unknown!<br/>";
                                        $errors = true;
                                        continue;
                                    }
                                    $r = $r[0];
                                    if ($r["exam_center_id"] <> $exam_center_id) {
                                        echo "Error: line " . ($i + 1) . ": student " . $student_id . " is not assigned to this exam center!<br/>";
                                        $errors = true;
                                        continue;
                                    }
                                    if ($r["room_number"] <> $room) {
                                        echo "Error: line " . ($i + 1) . ": student " . $student_id . " is not assigned to this room!<br/>";
                                        $errors = true;
                                        continue;
                                    }
                                    $students[$student_id] = array($r, $fields);
                                }
                                if ($errors)
                                    die();
                                echo count($students) . " students found.<br/>";

                                $query = "SELECT * FROM applicant_score_part_exam LEFT JOIN applicant ON applicant.applicant_id=applicant_score_part_exam.applicant_id LEFT JOIN subject_part ON subject_part.subject_part_id=applicant_score_part_exam.subject_part_id WHERE (";
                                $first = true;
                                foreach ($students as $student_id => $student) {
                                    if ($first)
                                        $first = false; else
                                        $query .= " OR ";
                                    $query .= "applicant.applicant_id=" . $student[0]["applicant_id"];
                                }
                                $query .= ") AND (";
                                $first = true;
                                foreach ($parts as $part) {
                                    if ($first)
                                        $first = false; else
                                        $query .= " OR ";
                                    $query .= "subject_part.subject_part_id=" . $part["subject_part_id"];
                                }
                                $query .= ")";
                                $result = DataBase::query($query);
                                if ($result <> false && count($result) > 0) {
                                    $arr = array();
                                    foreach ($result as $r)
                                        if ($r["score_part_exam"] <> null)
                                            array_push($arr, $r);
                                    if (count($arr)) {
                                        echo "Error: the following students already have exam results:<ul style='margin:0px'>";
                                        foreach ($arr as $r)
                                            echo "<li>Student ID " . $r["applicant_selection_id"] . " for part " . $r["subject_part_name"] . "</li>";
                                        echo "</ul>";
                                        DataBase::delete("concurrency", array("operation" => "import_amc", "username" => $_SESSION["user"]));
                                        die();
                                    }
                                }

                                $query = "SELECT * FROM applicant LEFT JOIN applicant_more_info ON applicant.applicant_id=applicant_more_info.applicant_id WHERE (";
                                $first = true;
                                foreach ($students as $student_id => $student) {
                                    if ($first)
                                        $first = false; else
                                        $query .= " OR ";
                                    $query .= "applicant.applicant_id=" . $student[0]["applicant_id"];
                                }
                                $query .= ") AND (applicant_more_info.applicant_more_info_attendee_exam_status IS NULL OR applicant_more_info.applicant_more_info_attendee_exam_status!=1)";
                                $result = DataBase::query($query);
                                if ($result <> false && count($result) > 0) {
                                    echo "Error: the following students have not their exam attendance field set to 'Yes':<ul style='margin:0px'>";
                                    foreach ($result as $r) {
                                        $val;
                                        if ($r["applicant_more_info_attendee_exam_status"] == null || $r["applicant_more_info_attendee_exam_status"] == 0)
                                            $val = "Not Yet";
                                        else
                                            switch ($r["applicant_more_info_attendee_exam_status"]) {
                                                case "2": $val = "No";
                                                    break;
                                                case "3": $val = "Partially";
                                                    break;
                                                case "4": $val = "Cheat";
                                                    break;
                                                default: $val = "Unknown value " . $r["applicant_more_info_attendee_exam_status"];
                                                    break;
                                            }
                                        echo "<li>Student ID " . $r["applicant_selection_id"] . ": Attendance=" . $val . "</li>";
                                    }
                                    echo "</ul>";
                                    DataBase::delete("concurrency", array("operation" => "import_amc", "username" => $_SESSION["user"]));
                                    die();
                                }

                                $score_part_exam = array();
                                $score_subject_exam = array();
                                foreach ($students as $student_id => $student) {
                                    $fields = $student[1];
                                    $global_marks = array();
                                    $copie = $fields[2];
                                    $pos = 3;
                                    foreach ($parts as $part) {
                                        $mark = 0;
                                        for ($i = 0; $i < $part["subject_part_nb_questions"]; $i++)
                                            $mark += floatval(str_replace(",", ".", trim($fields[$pos++])));
                                        $subject_id = $part["subject_id"];
                                        if (!isset($global_marks[$subject_id]))
                                            $global_marks[$subject_id] = 0;
                                        $global_marks[$subject_id] += $mark;
                                        array_push($score_part_exam, array("applicant_id" => $student[0]["applicant_id"], "subject_part_id" => $part["subject_part_id"], "score_part_exam" => $mark));
                                    }
                                    foreach ($global_marks as $subject_id => $global_mark)
                                        array_push($score_subject_exam, array("applicant_id" => $student[0]["applicant_id"], "score_subject_exam" => $global_mark, "subject_id" => $subject_id, "copie" => $copie, "subject_version" => $exam_version));
                                }
                                echo "Insert exam part results into database: ";
                                DataBase::insert_multiples("applicant_score_part_exam", $score_part_exam);
                                if (DataBase::last_error_number() <> 0) {
                                    echo "Failed: error #" . DataBase::last_error_number() . ": " . DataBase::last_error_message() . "<br/>";
                                    // TODO roll_back?
                                    DataBase::delete("concurrency", array("operation" => "import_amc", "username" => $_SESSION["user"]));
                                    die();
                                } else
                                    echo "OK<br/>";
                                echo "Insert exam subject global mark into database: ";
                                DataBase::insert_multiples("applicant_score_subject_exam", $score_subject_exam);
                                if (DataBase::last_error_number() <> 0) {
                                    echo "Failed: error #" . DataBase::last_error_number() . ": " . DataBase::last_error_message() . "<br/>";
                                    // TODO roll_back?
                                    DataBase::delete("concurrency", array("operation" => "import_amc", "username" => $_SESSION["user"]));
                                    die();
                                } else
                                    echo "OK<br/>";
                                echo "Exam results successfully imported into the database.";
                                DataBase::delete("concurrency", array("operation" => "import_amc", "username" => $_SESSION["user"]));
                            }
?>
                            <a href="status_view.php"> Status Overview</a><br />
                            <h2>Status Overview</h2>

<?php
                            require_once($_SESSION["root_path"] . "/exams/exam_queries.inc");
                            $q = query_exam_centers_having_rooms_and_exam_plan();
                            $q->join("province", null, array("province.province_id=exam_center.province_id"));
                            $q->order("province.province_name");
                            $q->order("exam_center.exam_center_name");
                            $q->select("province.*");
                            $q->select("exam_center.*");
                            $exam_centers = $q->execute();

                            $subjects = DataBase::query("SELECT * FROM subject WHERE selection_campaign_id=" . $_SESSION["campaign_id"]);

                            echo "<table border=1>";
                            echo "<tr><th>Province</th><th>Exam Center</th><th>Room</th><th>Attendees</th><th>All Marks</th>";
                            foreach ($subjects as $subject)
                                echo "<th>" . $subject["subject_name"] . "</th>";
                            echo "</tr>";

                            function query_attendees($center, $room) {
                                $q = new Query("applicant");
                                $q->join_applicant("applicant_exam_room_beta");
                                $q->where("applicant_exam_room_beta.exam_center_id=" . $center["exam_center_id"]);
                                $q->where("applicant_exam_room_beta.room_number=" . $room["room"]);
                                $q->join_applicant("applicant_more_info");
                                $q->where("applicant_more_info.applicant_more_info_attendee_exam_status=1");
                                return $q;
                            }

                            //echo $exam_centers[0][0];
                            if (isset($_POST["exam_center_id"])) {

                                foreach ($exam_centers as $center) {
                                    // echo $center["exam_center_id"]." , ";
                                    if ($center["exam_center_id"] == $_POST["exam_center_id"]) {
                                        echo "<tr>";
                                        $rooms = DataBase::query("SELECT * FROM exam_center_rooms WHERE exam_center_id=" . $center["exam_center_id"]);
                                        echo "<td rowspan=" . count($rooms) . " valign=middle>" . $center["province_name"] . "</td>";
                                        echo "<td rowspan=" . count($rooms) . " valign=middle>" . $center["exam_center_name"] . "</td>";
                                        $first_room = true;
                                        foreach ($rooms as $room) {
                                            if ($first_room)
                                                $first_room = false; else
                                                echo "<tr>";
                                            echo "<td>" . $room["room"] . "</td>";
                                            $q = query_attendees($center, $room);
                                            $attendees = $q->count();
                                            echo "<td align=center>" . $attendees . "</td>";
                                            $q = query_attendees($center, $room);
                                            foreach ($subjects as $subject) {
                                                $q->join_applicant("applicant_score_subject_exam", "s" . $subject["subject_id"], array("s" . $subject["subject_id"] . ".subject_id=" . $subject["subject_id"]));
                                                $q->where("s" . $subject["subject_id"] . ".score_subject_exam IS NOT NULL");
                                            }
                                            $ok = $q->count();
                                            echo "<td align=center style='color:" . ($ok == $attendees ? "green" : "red") . "'>" . $ok . "</td>";
                                            foreach ($subjects as $subject) {
                                                $q = query_attendees($center, $room);
                                                $q->join_applicant("applicant_score_subject_exam", null, array("applicant_score_subject_exam.subject_id=" . $subject["subject_id"]));
                                                $q->where("applicant_score_subject_exam.score_subject_exam IS NOT NULL");
                                                $ok = $q->count();
                                                $q = query_attendees($center, $room);
                                                $q->join_applicant("applicant_score_subject_exam", null, array("applicant_score_subject_exam.subject_id=" . $subject["subject_id"]));
                                                $q->where("applicant_score_subject_exam.subject_version='A'");
                                                $va = $q->count();
                                                $q = query_attendees($center, $room);
                                                $q->join_applicant("applicant_score_subject_exam", null, array("applicant_score_subject_exam.subject_id=" . $subject["subject_id"]));
                                                $q->where("applicant_score_subject_exam.subject_version='B'");
                                                $vb = $q->count();
                                                echo "<td align=center style='color:" . ($ok == $attendees ? "green" : "red") . "'>" . $ok;
                                                echo " (";
                                                if ($va > 0 && $ok < $attendees)
                                                    echo "<span style='color:blue'>";
                                                echo $va . "A";
                                                if ($va > 0 && $ok < $attendees)
                                                    echo "</span>";
                                                echo ",";
                                                if ($vb > 0 && $ok < $attendees)
                                                    echo "<span style='color:blue'>";
                                                echo $vb . "B";
                                                if ($vb > 0 && $ok < $attendees)
                                                    echo "</span>";
                                                echo ")</td>";
                                            }
                                            echo "</tr>";
                                        }
                                    }
                                }
                            }

                            echo "</table>";
?>
<?php
//require_once($_SESSION["root_path"]."/exams/exam_queries.inc");
//
//$q = query_exam_centers_having_rooms_and_exam_plan();
//$q->join("province", null, array("province.province_id=exam_center.province_id"));
//$q->order("province.province_name");
//$q->order("exam_center.exam_center_name");
//$q->select("province.*");
//$q->select("exam_center.*");
//$exam_centers = $q->execute();
//
//$subjects = DataBase::query("SELECT * FROM subject WHERE selection_campaign_id=".$_SESSION["campaign_id"]);
//
//echo "<table border=1>";
//echo "<tr><th>Province</th><th>Exam Center</th><th>Room</th><th>Attendees</th><th>All Marks</th>";
//foreach ($subjects as $subject) echo "<th>".$subject["subject_name"]."</th>";
//echo "</tr>";
//function query_attendees($center, $room) {
//	$q = new Query("applicant");
//	$q->join_applicant("applicant_exam_room_beta");
//	$q->where("applicant_exam_room_beta.exam_center_id=".$center["exam_center_id"]);
//	$q->where("applicant_exam_room_beta.room_number=".$room["room"]);
//	$q->join_applicant("applicant_more_info");
//	$q->where("applicant_more_info.applicant_more_info_attendee_exam_status=1");
//	return $q;
//}
//foreach ($exam_centers as $center) {
//	echo "<tr>";
//	$rooms = DataBase::query("SELECT * FROM exam_center_rooms WHERE exam_center_id=".$center["exam_center_id"]);
//	echo "<td rowspan=".count($rooms)." valign=middle>".$center["province_name"]."</td>";
//	echo "<td rowspan=".count($rooms)." valign=middle>".$center["exam_center_name"]."</td>";
//	$first_room = true;
//	foreach ($rooms as $room) {
//		if ($first_room) $first_room = false; else echo "<tr>";
//		echo "<td>".$room["room"]."</td>";
//		$q = query_attendees($center, $room);
//		$attendees = $q->count();
//		echo "<td align=center>".$attendees."</td>";
//		$q = query_attendees($center, $room);
//		foreach ($subjects as $subject) {
//			$q->join_applicant("applicant_score_subject_exam","s".$subject["subject_id"],array("s".$subject["subject_id"].".subject_id=".$subject["subject_id"]));
//			$q->where("s".$subject["subject_id"].".score_subject_exam IS NOT NULL");
//		}
//		$ok = $q->count();
//		echo "<td align=center style='color:".($ok == $attendees ? "green" : "red")."'>".$ok."</td>";
//		foreach ($subjects as $subject) {
//			$q = query_attendees($center, $room);
//			$q->join_applicant("applicant_score_subject_exam",null,array("applicant_score_subject_exam.subject_id=".$subject["subject_id"]));
//			$q->where("applicant_score_subject_exam.score_subject_exam IS NOT NULL");
//			$ok = $q->count();
//			$q = query_attendees($center, $room);
//			$q->join_applicant("applicant_score_subject_exam",null,array("applicant_score_subject_exam.subject_id=".$subject["subject_id"]));
//			$q->where("applicant_score_subject_exam.subject_version='A'");
//			$va = $q->count();
//			$q = query_attendees($center, $room);
//			$q->join_applicant("applicant_score_subject_exam",null,array("applicant_score_subject_exam.subject_id=".$subject["subject_id"]));
//			$q->where("applicant_score_subject_exam.subject_version='B'");
//			$vb = $q->count();
//			echo "<td align=center style='color:".($ok == $attendees ? "green" : "red")."'>".$ok;
//			echo " (";
//			if ($va > 0 && $ok < $attendees) echo "<span style='color:blue'>";
//			echo $va."A";
//			if ($va > 0 && $ok < $attendees) echo "</span>";
//			echo ",";
//			if ($vb > 0 && $ok < $attendees) echo "<span style='color:blue'>";
//			echo $vb."B";
//			if ($vb > 0 && $ok < $attendees) echo "</span>";
//			echo ")</td>";
//		}
//		echo "</tr>";
//	}
//}
//echo "</table>";
?>
